﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing.Imaging;
using AccessControl.Common;
using DevExpress.XtraEditors.Controls;


namespace AccessControl.DataAcess
{
    public static class PersonAccess
    {

        public static AccessControlDS.tblPersonDataTable SelectAll()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand("SELECT * FROM tblPerson", cn);
                var tblperson = new AccessControlDS.tblPersonDataTable();
                var pkPerson = "";
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        pkPerson = (string)reader["pkPerson"];
                        var newRow = tblperson.NewtblPersonRow();
                        newRow.pkPerson = (string)reader["pkPerson"];
                        newRow.PersonFirstName = Convert.ToString(reader["PersonFirstName"]);
                        newRow.PersonLastName = Convert.ToString(reader["PersonLastName"]);
                        if (reader["PersonPhoto"] != DBNull.Value)
                            newRow.PersonPhoto = (byte[])reader["PersonPhoto"];
                        else
                            newRow.PersonPhoto = ByteImageConverter.ToByteArray(Resource.nophoto);
                        if (reader["PersonTitle"] != DBNull.Value) newRow.PersonTitle = (byte)reader["PersonTitle"];
                        newRow.PersonDesc = Convert.ToString(reader["PersonDesc"]);
                        newRow.PersonIsMale = (bool)reader["PersonIsMale"];
                        if (reader["PersonInfoDate"] != DBNull.Value) newRow.PersonInfoDate = (DateTime)reader["PersonInfoDate"];
                        newRow.PersonRefNumber = Convert.ToString(reader["PersonRefNumber"]);
                        if (reader["PersonExpireDate"] != DBNull.Value) newRow.PersonExpireDate = (DateTime)reader["PersonExpireDate"];

                        tblperson.AddtblPersonRow(newRow);
                    }
                }
                catch (Exception ex)
                {
                    //throw new Exception(ex.Message + "," + pkPerson);
                }
                finally
                {
                    cn.Close();
                }
                return tblperson.Rows.Count != 0 ? tblperson : null;
            }
        }

        public static AccessControlDS.tblPersonDataTable SelectAllWithoutPhoto()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand("SELECT pkPerson, PersonFirstName, PersonLastName,PersonTitle, PersonDesc, PersonIsMale, PersonInfoDate, PersonRefNumber, PersonExpireDate,ISNULL(PersonFirstName,'')+' '+ISNULL(PersonLastName,'') as PersonFullName FROM tblPerson", cn);
                var tblperson = new AccessControlDS.tblPersonDataTable();
                var pkPerson = "";
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblperson.NewtblPersonRow();
                        pkPerson = (string)reader["pkPerson"];
                        newRow.pkPerson = (string)reader["pkPerson"];
                        newRow.PersonFirstName = Convert.ToString(reader["PersonFirstName"]);
                        newRow.PersonLastName = Convert.ToString(reader["PersonLastName"]);
                        newRow.PersonPhoto = new byte[0];
                        if (reader["PersonTitle"] != DBNull.Value) newRow.PersonTitle = (byte)reader["PersonTitle"];
                        newRow.PersonDesc = Convert.ToString(reader["PersonDesc"]);
                        newRow.PersonIsMale = (bool)reader["PersonIsMale"];
                        if (reader["PersonInfoDate"] != DBNull.Value) newRow.PersonInfoDate = (DateTime)reader["PersonInfoDate"];
                        newRow.PersonRefNumber = Convert.ToString(reader["PersonRefNumber"]);
                        if (reader["PersonExpireDate"] != DBNull.Value) newRow.PersonExpireDate = (DateTime)reader["PersonExpireDate"];

                        tblperson.AddtblPersonRow(newRow);
                    }
                }
                catch (Exception ex)
                {
                    //throw new ApplicationException(ex.Message + "," + pkPerson);
                }
                finally
                {
                    cn.Close();
                }
                return tblperson.Rows.Count != 0 ? tblperson : null;
            }
        }

        public static AccessControlDS.tblPersonDataTable SelectByFilter(string filter)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblPerson where {0}", filter), cn);
                var tblperson = new AccessControlDS.tblPersonDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblperson.NewtblPersonRow();
                        newRow.pkPerson = (string)reader["pkPerson"];
                        newRow.PersonFirstName = Convert.ToString(reader["PersonFirstName"]);
                        newRow.PersonLastName = Convert.ToString(reader["PersonLastName"]);
                        if (reader["PersonPhoto"] != DBNull.Value)
                            newRow.PersonPhoto = (byte[])reader["PersonPhoto"];
                        else
                            newRow.PersonPhoto = ByteImageConverter.ToByteArray(Resource.nophoto, ImageFormat.Bmp);
                        if (reader["PersonTitle"] != DBNull.Value) newRow.PersonTitle = (byte)reader["PersonTitle"];
                        newRow.PersonDesc = Convert.ToString(reader["PersonDesc"]);
                        newRow.PersonIsMale = (bool)reader["PersonIsMale"];
                        if (reader["PersonInfoDate"] != DBNull.Value) newRow.PersonInfoDate = (DateTime)reader["PersonInfoDate"];
                        newRow.PersonRefNumber = Convert.ToString(reader["PersonRefNumber"]);
                        if (reader["PersonExpireDate"] != DBNull.Value) newRow.PersonExpireDate = (DateTime)reader["PersonExpireDate"];

                        tblperson.AddtblPersonRow(newRow);
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    cn.Close();
                }
                return tblperson.Rows.Count != 0 ? tblperson : null;
            }
        }
        
        public static void Insert(AccessControlDS.tblPersonRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdInsert = new SqlCommand("spPerson_Insert", cn) { CommandType = CommandType.StoredProcedure };
                cmdInsert.Parameters.AddWithValue("@pkPerson", row.pkPerson);
                cmdInsert.Parameters.AddWithValue("@PersonFirstName", row.PersonFirstName);
                cmdInsert.Parameters.AddWithValue("@PersonLastName", row.PersonLastName);
                if (!row.IsPersonPhotoNull()) cmdInsert.Parameters.AddWithValue("@PersonPhoto", row.PersonPhoto);
                cmdInsert.Parameters.AddWithValue("@PersonTitle", row.PersonTitle);
                cmdInsert.Parameters.AddWithValue("@PersonDesc", row.PersonDesc);
                cmdInsert.Parameters.AddWithValue("@PersonIsMale", row.PersonIsMale);
                cmdInsert.Parameters.AddWithValue("@PersonInfoDate", row.PersonInfoDate);
                cmdInsert.Parameters.AddWithValue("@PersonRefNumber", row.PersonRefNumber);
                if (!row.IsPersonExpireDateNull()) cmdInsert.Parameters.AddWithValue("@PersonExpireDate", row.PersonExpireDate);
                try
                {
                    cn.Open();
                    cmdInsert.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Update(AccessControlDS.tblPersonRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdUpdate = new SqlCommand("spPerson_Update", cn) { CommandType = CommandType.StoredProcedure };
                cmdUpdate.Parameters.AddWithValue("@pkPerson", row.pkPerson);
                cmdUpdate.Parameters.AddWithValue("@PersonFirstName", row.PersonFirstName);
                cmdUpdate.Parameters.AddWithValue("@PersonLastName", row.PersonLastName);
                if (!row.IsPersonPhotoNull()) cmdUpdate.Parameters.AddWithValue("@PersonPhoto", row.PersonPhoto);
                cmdUpdate.Parameters.AddWithValue("@PersonTitle", row.PersonTitle);
                cmdUpdate.Parameters.AddWithValue("@PersonDesc", row.PersonDesc);
                cmdUpdate.Parameters.AddWithValue("@PersonIsMale", row.PersonIsMale);
                cmdUpdate.Parameters.AddWithValue("@PersonInfoDate", row.PersonInfoDate);
                cmdUpdate.Parameters.AddWithValue("@PersonRefNumber", row.PersonRefNumber);
                if (!row.IsPersonExpireDateNull()) cmdUpdate.Parameters.AddWithValue("@PersonExpireDate", row.PersonExpireDate);
                try
                {
                    cn.Open();
                    cmdUpdate.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Delete(AccessControlDS.tblPersonRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdDelete = new SqlCommand("spPerson_Delete", cn) { CommandType = CommandType.StoredProcedure };
                cmdDelete.Parameters.AddWithValue("@pkPerson", row.pkPerson);
                try
                {
                    cn.Open();
                    cmdDelete.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Delete(string pkPerson)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdDelete = new SqlCommand("spPerson_Delete", cn) { CommandType = CommandType.StoredProcedure };
                cmdDelete.Parameters.AddWithValue("@pkPerson", pkPerson);
                try
                {
                    cn.Open();
                    cmdDelete.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static AccessControlDS.tblPersonDataTable SelectByFilter(string perosnRefNumber, string personFirstName, string personLastName)
        {
            var whereClause = "";
            if (perosnRefNumber != String.Empty)
                whereClause += " AND PersonRefNumber LIKE @PersonRefNumber +'%'";
            if (personFirstName != String.Empty)
                whereClause += " AND PersonFirstName LIKE @PersonFirstName +'%'";
            if (personLastName != String.Empty)
                whereClause += " AND PersonLastName LIKE @PersonLastName +'%'";
            

            
                using (var cn = new SqlConnection(Util.ConnectionString))
                {
                    var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblPerson where (1=1) {0}", whereClause), cn);
                    cmdSelect.Parameters.AddWithValue("@PersonRefNumber", perosnRefNumber);
                    cmdSelect.Parameters.AddWithValue("@PersonFirstName", personFirstName);
                    cmdSelect.Parameters.AddWithValue("@PersonLastName", personLastName);
                    var tblperson = new AccessControlDS.tblPersonDataTable();
                    try
                    {
                        cn.Open();
                        var reader = cmdSelect.ExecuteReader();
                        while (reader.Read())
                        {
                            var newRow = tblperson.NewtblPersonRow();
                            newRow.pkPerson = (string)reader["pkPerson"];
                            newRow.PersonFirstName = Convert.ToString(reader["PersonFirstName"]);
                            newRow.PersonLastName = Convert.ToString(reader["PersonLastName"]);
                            if (reader["PersonPhoto"] != DBNull.Value)
                                newRow.PersonPhoto = (byte[])reader["PersonPhoto"];
                            else
                                newRow.PersonPhoto = ByteImageConverter.ToByteArray(Resource.nophoto, ImageFormat.Bmp);
                            if (reader["PersonTitle"] != DBNull.Value) newRow.PersonTitle = (byte)reader["PersonTitle"];
                            newRow.PersonDesc = Convert.ToString(reader["PersonDesc"]);
                            newRow.PersonIsMale = (bool)reader["PersonIsMale"];
                            if (reader["PersonInfoDate"] != DBNull.Value) newRow.PersonInfoDate = (DateTime)reader["PersonInfoDate"];
                            newRow.PersonRefNumber = Convert.ToString(reader["PersonRefNumber"]);
                            if (reader["PersonExpireDate"] != DBNull.Value) newRow.PersonExpireDate = (DateTime)reader["PersonExpireDate"];

                            tblperson.AddtblPersonRow(newRow);
                        }
                    }
                    catch (Exception)
                    {
                    }
                    finally
                    {
                        cn.Close();
                    }
                    return tblperson.Rows.Count != 0 ? tblperson : null;
                
            }
        }
       
    }
}
